package com.bw.mapper;

import com.bw.bean.Vo;
import com.bw.bean.Vo1;
import com.bw.bean.Vo2;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface Vomapper {
@Select("select ch.chapter_name 'name',count( ucp.id) 'num',\n" +
        "sum(ucp.position_sec) 'time',count(distinct ucp.user_id) 'num1',\n" +
        "sum(ucp.position_sec)/count(distinct ucp.user_id) 'avgtime'\n" +
        "from user_chapter_process ucp\n" +
        "left join chapter_info ch on ch.id = ucp.chapter_id\n" +
        "group by  ch.chapter_name")
    List<Vo> getlist1();
@Select("select ba.source_site 'name',sum(oi.final_amount) 'sal',count(distinct oi.user_id) 'num',\n" +
        "       count(oi.id) 'num1'\n" +
        "from base_source ba\n" +
        "left join video_info vi on ba.id = vi.video_source_id\n" +
        "left join chapter_info ci on vi.id = ci.video_id\n" +
        "left join course_info c on c.id = ci.course_id\n" +
        "left join order_detail od on c.id = od.course_id\n" +
        "left join order_info oi on oi.id = od.order_id\n" +
        "group by  ba.source_site")
    List<Vo> getlist2();
@Select("select count(*) from order_info where create_time>'2024-1-1'")
    Double getlist3();
@Select("select course_name 'name',avg(ri.review_stars) 'num',\n" +
        "       count(distinct ri.user_id) 'time',\n" +
        "       sum(if(ri.review_stars>3,1,0)) 'num1',\n" +
        "       sum(if(ri.review_stars>3,1,0))/count(distinct ri.user_id) 'avgtime'\n" +
        "from review_info ri\n" +
        "left join course_info co on co.id = ri.course_id\n" +
        "group by co.course_name")
    List<Vo> getlist4();
@Select("select co.course_name 'name',count(distinct te.user_id) 'sal',\n" +
        " avg(te.score) 'num', avg(te.duration_sec) 'num1'\n" +
        "from  test_exam te\n" +
        "left join test_paper tp on tp.id = te.paper_id\n" +
        "left join course_info co on co.id = tp.course_id\n" +
        "group by co.course_name")
    List<Vo1> getlist5();
@Select("select ba.source_site 'name',count(distinct ui.id) 'value' from base_source ba\n" +
        "left join video_info vi on ba.id = vi.video_source_id\n" +
        "left join chapter_info ci on vi.id = ci.video_id\n" +
        "left join course_info c on c.id = ci.course_id\n" +
        "left join order_detail od on c.id = od.course_id\n" +
        "left join user_info ui on ui.id = od.user_id\n" +
        "group by ba.source_site")
    List<Vo2> getlist6();
@Select("select te.paper_title 'name',count(distinct t.user_id) 'sal',\n" +
        "       avg(t.score) 'num',avg(t.duration_sec)'num1'\n" +
        "       from  test_exam t\n" +
        "left join test_paper te on te.id = t.paper_id\n" +
        "group by te.paper_title")
    List<Vo1> getlist7();
@Select("select  hour(create_time) 'name',count(distinct user_id) 'value' from user_chapter_process\n" +
        "  group by hour(create_time) order by hour(create_time)")
    List<Vo2> getlist8();
}
